package com.book.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.book.bean.Book;
import com.book.dao.BookDao;
import com.book.util.DBUtil;

public class BookDaoImpl implements BookDao {

	@Override
	public boolean addBook(Book book) throws Exception {
		Connection conn = DBUtil.getConn();
		String sql = "insert into book(bname,bpicture,classify,author,publisher,date_publish,cost,hintcount) values(?,?,?,?,?,?,?,0)";
		
		ArrayList<Object> param = new ArrayList<Object>();
		param.add(book.getBname());
		param.add(book.getBpicture());
		param.add(book.getClassify());
		param.add(book.getAuthor());
		param.add(book.getPublisher());
		param.add(book.getDate_publish());
		param.add(book.getCost());
		
		return DBUtil.add(sql, param) > 0;
	}

	@Override
	public List<Book> listBook() throws Exception {
		List<Book> list = new ArrayList<Book>();
		Connection conn = DBUtil.getConn();
		String sql = "select bid,bname,bpicture,classify,author,publisher,date_publish,cost,hintcount from book";
		PreparedStatement psmt = conn.prepareStatement(sql);
		ResultSet rs = psmt.executeQuery();
		while(rs.next()){
			Book book = new Book();
			book.setBid(rs.getInt(1));
			book.setBname(rs.getString(2));
			book.setBpicture(rs.getString(3));
			book.setClassify(rs.getString(4));
			book.setAuthor(rs.getString(5));
			book.setPublisher(rs.getString(6));
			book.setDate_publish(rs.getString(7));
			book.setCost(rs.getDouble(8));
			book.setHintcount(rs.getInt(9));
			list.add(book);
		}
		rs.close();
		psmt.close();
		
		return list;
	}

	@Override
	public Book getOneBook(int bid) throws Exception {
		Connection conn = DBUtil.getConn();
		String sql = "select bid,bname,bpicture,classify,author,publisher,date_publish,cost,hintcount from book where bid=?";
		PreparedStatement psmt = conn.prepareStatement(sql);
		psmt.setInt(1, bid);
		ResultSet rs = psmt.executeQuery();
		while (rs.next()) {
			Book book = new Book();
			book.setBid(rs.getInt(1));
			book.setBname(rs.getString(2));
			book.setBpicture(rs.getString(3));
			book.setClassify(rs.getString(4));
			book.setAuthor(rs.getString(5));
			book.setPublisher(rs.getString(6));
			book.setDate_publish(rs.getString(7));
			book.setCost(rs.getDouble(8));
			book.setHintcount(rs.getInt(9));
			return book;
		}
		rs.close();
		psmt.close();
		return null;
	}

	@Override
	public List<Book> recomBook() throws Exception {
		List<Book> list = new ArrayList<Book>();
		Connection conn = DBUtil.getConn();
		String sql = "select bid,bname,bpicture,classify,author,publisher,date_publish,cost,hintcount from book order by hintcount desc limit 3";
		PreparedStatement psmt = conn.prepareStatement(sql);
		ResultSet rs = psmt.executeQuery();
		while(rs.next()){
			Book book = new Book();
			book.setBid(rs.getInt(1));
			book.setBname(rs.getString(2));
			book.setBpicture(rs.getString(3));
			book.setClassify(rs.getString(4));
			book.setAuthor(rs.getString(5));
			book.setPublisher(rs.getString(6));
			book.setDate_publish(rs.getString(7));
			book.setCost(rs.getDouble(8));
			book.setHintcount(rs.getInt(9));
			list.add(book);
		}
		rs.close();
		psmt.close();
		
		return list;
	}

	@Override
	public List<Book> keySearchBook(String keyword) throws Exception {
		List<Book> list = new ArrayList<Book>();
		Connection conn = DBUtil.getConn();
		String sql = "select bid,bname,bpicture,classify,author,publisher,date_publish,cost,hintcount from book where concat(bname,classify,author,publisher) like ?";
		PreparedStatement psmt = conn.prepareStatement(sql);
		psmt.setString(1, "%"+keyword+"%");
		ResultSet rs = psmt.executeQuery();
		while(rs.next()){
			Book book = new Book();
			book.setBid(rs.getInt(1));
			book.setBname(rs.getString(2));
			book.setBpicture(rs.getString(3));
			book.setClassify(rs.getString(4));
			book.setAuthor(rs.getString(5));
			book.setPublisher(rs.getString(6));
			book.setDate_publish(rs.getString(7));
			book.setCost(rs.getDouble(8));
			book.setHintcount(rs.getInt(9));
			list.add(book);
		}
		rs.close();
		psmt.close();
		
		return list;
	}

	@Override
	public List<String> allIndex() throws Exception {
		List<String> list = new ArrayList<String>();
		Connection conn = DBUtil.getConn();
		String sql = "select bname,classify,author,publisher from book";
		PreparedStatement psmt = conn.prepareStatement(sql);
		ResultSet rs = psmt.executeQuery();
		while(rs.next()){
			list.add(rs.getString(1));
			list.add(rs.getString(2));
			list.add(rs.getString(3));
			list.add(rs.getString(4));
		}
		rs.close();
		psmt.close();
		
		return list;
	}

	@Override
	public boolean modifyBook(int bid,int hintcount) throws Exception {
		Connection conn = DBUtil.getConn();
		String sql = "update book set hintcount="+hintcount+" where bid=?";
		ArrayList<Object> param = new ArrayList<Object>();
		param.add(bid);
		return DBUtil.update(sql, param)>0;
	}
	

}
